﻿#Region "Imports"
Imports System.IO
Imports Microsoft.LightSwitch.Presentation
Imports Microsoft.LightSwitch.Presentation.Extensions
Imports Microsoft.LightSwitch.Client
Imports Microsoft.LightSwitch
Imports Microsoft.LightSwitch.Details
Imports Microsoft.LightSwitch.Model
Imports System.Runtime.CompilerServices
Imports Microsoft.LightSwitch.Threading
Imports System.Diagnostics
Imports Microsoft.LightSwitch.Framework
Imports System.Reflection
Imports System.Security
Imports System.Runtime.InteropServices
Imports System.Linq.Expressions
Imports Microsoft.LightSwitch.Sdk.Proxy
Imports Microsoft.VisualStudio.ExtensibilityHosting
Imports Microsoft.LightSwitch.Runtime.Shell.Framework
Imports System.Windows.Interop
Imports System.Runtime.InteropServices.Automation
Imports System.Collections
#End Region

Public Module Excel
    Private _excelDocRange As String(,)
    Private _collection As IVisualCollection
    Private _columnMappings As List(Of ColumnMapping)
    Private navProperties As Dictionary(Of String, IEntityObject) = New Dictionary(Of String, IEntityObject)
    Private _fileInfo As FileInfo

#Region "Export Overloads"
    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IVisualCollection, Workbook As String, Worksheet As String, Range As String) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)
                ws.Activate()
                If Not ws Is Nothing Then
                    rg = ws.Range(Range)
                    rg.Activate()
                    Dim entityObj As IEntityObject
                    Dim columnNames As List(Of String) = New List(Of String)

                    If collection.Count > 0 Then

                        ' get column properties
                        Dim columnProperties As IEnumerable(Of IEntityProperty) = collection(0).Details.Properties.All()

                        Dim entityProperty As IEntityProperty
                        Dim columnCounter As Integer = 0
                        Dim rowCounter As Integer = 0

                        ' add columns names to the list
                        For Each entityProperty In columnProperties
                            columnNames.Add(entityProperty.Name)

                            rg.Offset(rowCounter, columnCounter).Value = entityProperty.DisplayName
                            columnCounter += 1
                        Next

                        ' add values on the row following the headers
                        rowCounter = 1

                        ' iterate the collection and extract values by column name
                        For Each entityObj In collection
                            For i As Integer = 0 To columnNames.Count - 1
                                rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetValue(entityObj, columnNames(i))
                            Next
                            rowCounter += 1
                        Next

                    End If

                    result = wb
                    xlProxy.ShowWorkbook()

                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()

            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        Export = result
    End Function

    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IVisualCollection, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of String)) As Object

        Dim mappings As New List(Of ColumnMapping)
        Dim map As ColumnMapping
        For Each name As String In ColumnNames
            map = New ColumnMapping("", name)
            map.TableField.DisplayName = name
            mappings.Add(map)
        Next

        Export = Export(collection, Workbook, Worksheet, Range, mappings)
    End Function

    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IVisualCollection, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of ColumnMapping)) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)
                ws.Activate()
                If Not ws Is Nothing Then
                    rg = ws.Range(Range)
                    rg.Activate()
                    Dim entityObj As IEntityObject

                    If collection.Count > 0 Then

                        ' get column properties
                        Dim columnCounter As Integer = 0
                        Dim rowCounter As Integer = 0
                        Dim map As ColumnMapping

                        ' add columns names to the list
                        For Each map In ColumnNames
                            If map.TableField.DisplayName.Length > 0 Then
                                rg.Offset(rowCounter, columnCounter).Value = map.TableField.DisplayName
                            Else
                                rg.Offset(rowCounter, columnCounter).Value = map.TableField.Name
                            End If
                            columnCounter += 1
                        Next

                        ' add values on the row following the headers
                        rowCounter = 1

                        ' iterate the collection and extract values by column name
                        For Each entityObj In collection
                            For i As Integer = 0 To ColumnNames.Count - 1
                                Try
                                    Dim sValue As Object

                                    ' passed in a static value so use that instead
                                    If ColumnNames(i).TableField.StaticValue IsNot Nothing Then
                                        sValue = ColumnNames(i).TableField.StaticValue.ToString()
                                    Else
                                        sValue = LightSwitchHelper.GetValue(entityObj, ColumnNames(i).TableField.Name)

                                        ' check to see if we have to format the value
                                        If ColumnNames(i).TableField.FormatDelegate IsNot Nothing Then
                                            Try
                                                ' Catch possible improperly formatted delegates
                                                Dim paramType As Type = ColumnNames(i).TableField.FormatDelegate.Method.GetParameters()(0).ParameterType
                                                sValue = ColumnNames(i).TableField.FormatDelegate.DynamicInvoke(Convert.ChangeType(sValue, paramType, Nothing)).ToString()
                                            Catch ex As Exception
                                                Debug.WriteLine(ex.Message)
                                            End Try
                                        End If
                                    End If

                                    rg.Offset(rowCounter, i).Value = sValue
                                Catch ex As Exception
                                End Try
                            Next
                            rowCounter += 1
                        Next

                    End If

                    result = wb
                    xlProxy.ShowWorkbook()

                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()
            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet
    Public Function Export(ByVal collection As IVisualCollection) As Object
        Dim result As Object = Nothing

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As IEntityObject
            Dim columnNames As List(Of String) = New List(Of String)

            If collection.Count > 0 Then

                ' get column properties
                Dim columnProperties As IEnumerable(Of IEntityProperty) = collection(0).Details.Properties.All()

                Dim entityProperty As IEntityProperty
                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1

                ' add columns names to the list
                For Each entityProperty In columnProperties
                    columnNames.Add(entityProperty.Name)

                    ' add column headers to Excel workbook
                    excel.Cells(rowCounter, columnCounter, entityProperty.DisplayName)
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To columnNames.Count - 1
                        excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, columnNames(i)))
                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet with specific fields
    Public Function Export(ByVal collection As IVisualCollection, ColumnNames As List(Of String)) As Object
        Dim result As Object = Nothing

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As IEntityObject

            If collection.Count > 0 Then

                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1
                Dim columnName As String
                ' add columns names to the list
                For Each columnName In ColumnNames
                    ' add column headers to Excel workbook
                    excel.Cells(rowCounter, columnCounter, columnName)
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To ColumnNames.Count - 1
                        excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, ColumnNames(i)))
                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet with specific fields
    Public Function Export(ByVal collection As IVisualCollection, ColumnNames As List(Of ColumnMapping)) As Object
        Dim result As Object = Nothing

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As IEntityObject

            If collection.Count > 0 Then

                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1
                Dim map As ColumnMapping

                ' add columns names to the list
                For Each map In ColumnNames
                    If map.TableField.DisplayName.Length > 0 Then
                        excel.Cells(rowCounter, columnCounter, map.TableField.DisplayName)
                    Else
                        excel.Cells(rowCounter, columnCounter, map.TableField.Name)
                    End If
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To ColumnNames.Count - 1
                        Try
                            Dim sValue As Object

                            ' passed in a static value so use that instead
                            If ColumnNames(i).TableField.StaticValue IsNot Nothing Then
                                sValue = ColumnNames(i).TableField.StaticValue.ToString()
                            Else
                                sValue = LightSwitchHelper.GetValue(entityObj, ColumnNames(i).TableField.Name)

                                ' check to see if we have to format the value
                                If ColumnNames(i).TableField.FormatDelegate IsNot Nothing Then
                                    Try
                                        ' Catch possible improperly formatted delegates
                                        Dim paramType As Type = ColumnNames(i).TableField.FormatDelegate.Method.GetParameters()(0).ParameterType
                                        sValue = ColumnNames(i).TableField.FormatDelegate.DynamicInvoke(Convert.ChangeType(sValue, paramType, Nothing)).ToString()
                                    Catch ex As Exception
                                        Debug.WriteLine(ex.Message)
                                    End Try
                                End If
                            End If
                            excel.Cells(rowCounter, i + 1, sValue)
                        Catch ex As Exception
                        End Try
                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

#End Region

#Region "IEnumerable Overloads"
    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IEnumerable, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of String)) As Object
        Dim mappings As New List(Of ColumnMapping)
        Dim map As ColumnMapping
        For Each name As String In ColumnNames
            map = New ColumnMapping("", name)
            map.TableField.DisplayName = name
            mappings.Add(map)
        Next

        Export = Export(collection, Workbook, Worksheet, Range, mappings)
    End Function

    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IEnumerable, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of ColumnMapping)) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing
        Dim count As Integer

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)
                ws.Activate()
                If Not ws Is Nothing Then
                    rg = ws.Range(Range)
                    rg.Activate()
                    Dim entityObj As Object

                    count = (From c In collection
                            Select c).Count()

                    If count > 0 Then

                        ' get column properties
                        Dim columnCounter As Integer = 0
                        Dim rowCounter As Integer = 0
                        Dim map As ColumnMapping

                        ' add columns names to the table
                        For Each map In ColumnNames
                            If map.TableField.DisplayName.Length > 0 Then
                                rg.Offset(rowCounter, columnCounter).Value = map.TableField.DisplayName
                            Else
                                rg.Offset(rowCounter, columnCounter).Value = map.TableField.Name
                            End If
                            columnCounter += 1
                        Next

                        ' add values on the row following the headers
                        rowCounter = 1

                        ' iterate the collection and extract values by column name
                        For Each entityObj In collection
                            For i As Integer = 0 To ColumnNames.Count - 1
                                Try
                                    Dim sValue As Object

                                    ' passed in a static value so use that instead
                                    If ColumnNames(i).TableField.StaticValue IsNot Nothing Then
                                        sValue = ColumnNames(i).TableField.StaticValue.ToString()
                                    Else
                                        If TypeOf entityObj Is IBusinessObject Then
                                            sValue = LightSwitchHelper.GetValue(entityObj, ColumnNames(i).TableField.Name)
                                        Else
                                            sValue = LightSwitchHelper.GetObjectValue(entityObj, ColumnNames(i).TableField.Name)
                                        End If

                                        ' check to see if we have to format the value
                                        If ColumnNames(i).TableField.FormatDelegate IsNot Nothing Then
                                            Try
                                                ' Catch possible improperly formatted delegates
                                                Dim paramType As Type = ColumnNames(i).TableField.FormatDelegate.Method.GetParameters()(0).ParameterType
                                                sValue = ColumnNames(i).TableField.FormatDelegate.DynamicInvoke(Convert.ChangeType(sValue, paramType, Nothing)).ToString()
                                            Catch ex As Exception
                                            End Try
                                        End If
                                    End If

                                    rg.Offset(rowCounter, i).Value = sValue
                                Catch ex As Exception
                                End Try
                            Next
                            rowCounter += 1
                        Next

                    End If

                    result = wb
                    xlProxy.ShowWorkbook()
                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()
            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet
    Public Function Export(ByVal collection As IEnumerable, ColumnNames As List(Of String)) As Object
        Dim result As Object = Nothing
        Dim count As Integer

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As Object

            count = (From c In collection
                    Select c).Count()

            If count > 0 Then

                ' get column properties
                Dim columnName As String
                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1

                ' add columns names to the table
                For Each columnName In ColumnNames
                    ' add column headers to Excel workbook
                    excel.Cells(rowCounter, columnCounter, columnName)
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To ColumnNames.Count - 1
                        If TypeOf entityObj Is IBusinessObject Then
                            excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, ColumnNames(i)).ToString())
                        Else
                            excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetObjectValue(entityObj, ColumnNames(i)).ToString())
                        End If

                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet
    Public Function Export(ByVal collection As IEnumerable, ColumnNames As List(Of ColumnMapping)) As Object
        Dim result As Object = Nothing
        Dim count As Integer

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As Object

            count = (From c In collection
                    Select c).Count()

            If count > 0 Then

                ' get column properties
                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1
                Dim map As ColumnMapping

                ' add columns names to the table
                For Each map In ColumnNames
                    If map.TableField.DisplayName.Length > 0 Then
                        excel.Cells(rowCounter, columnCounter, map.TableField.DisplayName)
                    Else
                        excel.Cells(rowCounter, columnCounter, map.TableField.Name)
                    End If
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To ColumnNames.Count - 1
                        Try
                            Dim sValue As Object

                            ' passed in a static value so use that instead
                            If ColumnNames(i).TableField.StaticValue IsNot Nothing Then
                                sValue = ColumnNames(i).TableField.StaticValue.ToString()
                            Else
                                If TypeOf entityObj Is IBusinessObject Then
                                    sValue = LightSwitchHelper.GetValue(entityObj, ColumnNames(i).TableField.Name)
                                Else
                                    sValue = LightSwitchHelper.GetObjectValue(entityObj, ColumnNames(i).TableField.Name)
                                End If

                                ' check to see if we have to format the value
                                If ColumnNames(i).TableField.FormatDelegate IsNot Nothing Then
                                    Try
                                        ' Catch possible improperly formatted delegates
                                        Dim paramType As Type = ColumnNames(i).TableField.FormatDelegate.Method.GetParameters()(0).ParameterType
                                        sValue = ColumnNames(i).TableField.FormatDelegate.DynamicInvoke(Convert.ChangeType(sValue, paramType, Nothing)).ToString()
                                    Catch ex As Exception
                                    End Try
                                End If
                            End If
                            excel.Cells(rowCounter, i + 1, sValue)
                        Catch ex As Exception
                        End Try
                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet
    Public Function Export(ByVal collection As IEnumerable) As Object
        Dim result As Object = Nothing

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As Object
            Dim columnNames As List(Of String) = New List(Of String)

            Dim count = (From c In collection
                        Select c).Count()

            If count > 0 Then

                ' get column properties
                Dim columnProperties As IEnumerable(Of System.Reflection.PropertyInfo) = collection(0).GetType().GetProperties().AsEnumerable()

                Dim entityProperty As System.Reflection.PropertyInfo
                Dim columnCounter As Integer = 1
                Dim rowCounter As Integer = 1

                ' add columns names to the list
                For Each entityProperty In columnProperties
                    columnNames.Add(entityProperty.Name)

                    ' add column headers to Excel workbook
                    excel.Cells(rowCounter, columnCounter, entityProperty.Name)
                    columnCounter += 1
                Next

                ' add values on the row following the headers
                rowCounter = 2

                ' iterate the collection and extract values by column name
                For Each entityObj In collection
                    For i As Integer = 0 To columnNames.Count - 1
                        Try
                            If TypeOf entityObj Is IBusinessObject Then
                                excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, columnNames(i)).ToString())
                            Else
                                excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetObjectValue(entityObj, columnNames(i)).ToString())
                            End If
                        Catch ex As Exception
                            excel.Cells(rowCounter, i + 1, entityObj.GetType().ToString())
                        End Try
                    Next
                    rowCounter += 1
                Next

            End If

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        Export = result
    End Function

    ' exports a collection to a workbook starting at the specified location
    Public Function Export(ByVal collection As IEnumerable, Workbook As String, Worksheet As String, Range As String) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)
                ws.Activate()
                If Not ws Is Nothing Then
                    rg = ws.Range(Range)
                    rg.Activate()
                    Dim entityObj As Object
                    Dim columnNames As List(Of String) = New List(Of String)

                    Dim count = (From c In collection
                                Select c).Count()

                    If count > 0 Then

                        ' get column properties
                        Dim columnProperties As IEnumerable(Of System.Reflection.PropertyInfo) = collection(0).GetType().GetProperties().AsEnumerable()

                        Dim entityProperty As PropertyInfo
                        Dim columnCounter As Integer = 0
                        Dim rowCounter As Integer = 0

                        ' add columns names to the list
                        For Each entityProperty In columnProperties
                            columnNames.Add(entityProperty.Name)

                            rg.Offset(rowCounter, columnCounter).Value = entityProperty.Name
                            columnCounter += 1
                        Next

                        ' add values on the row following the headers
                        rowCounter = 1

                        ' iterate the collection and extract values by column name
                        For Each entityObj In collection
                            For i As Integer = 0 To columnNames.Count - 1
                                Try
                                    If TypeOf entityObj Is IBusinessObject Then
                                        rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetValue(entityObj, columnNames(i)).ToString()
                                    Else
                                        rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetObjectValue(entityObj, columnNames(i)).ToString()
                                    End If
                                    rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetObjectValue(entityObj, columnNames(i))
                                Catch ex As Exception
                                    rg.Offset(rowCounter, i).Value = entityObj.GetType().ToString()
                                End Try
                            Next
                            rowCounter += 1
                        Next

                    End If

                    result = wb
                    xlProxy.ShowWorkbook()

                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()

            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        Export = result
    End Function
#End Region

#Region "ExportEntityCollection Overloads"
    ' exports an IEntityCollection to a workbook starting at the specified location
    <Obsolete("This method has been deprecated. Please use Excel.Export() overloads.")>
    Public Function ExportEntityCollection(ByVal collection As IEntityCollection, Workbook As String, Worksheet As String, Range As String) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)

                If Not ws Is Nothing Then
                    rg = ws.Range(Range)

                    Dim entityObj As IEntityObject
                    Dim columnNames As List(Of String) = New List(Of String)

                    ' get column properties
                    Dim columnProperties As IEnumerable(Of IEntityProperty) = collection(0).Details.Properties.All()

                    Dim entityProperty As IEntityProperty
                    Dim columnCounter As Integer = 0
                    Dim rowCounter As Integer = 0

                    ' add columns names to the list
                    For Each entityProperty In columnProperties
                        columnNames.Add(entityProperty.Name)

                        rg.Offset(rowCounter, columnCounter).Value = entityProperty.DisplayName
                        columnCounter += 1
                    Next

                    ' add values on the row following the headers
                    rowCounter = 1

                    ' iterate the collection and extract values by column name
                    For Each entityObj In collection
                        For i As Integer = 0 To columnNames.Count - 1
                            rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetValue(entityObj, columnNames(i))
                        Next
                        rowCounter += 1
                    Next

                    result = wb
                    xlProxy.ShowWorkbook()

                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()

            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        ExportEntityCollection = result
    End Function

    ' exports a collection to a workbook starting at the specified location
    <Obsolete("This method has been deprecated. Please use Excel.Export() overloads.")>
    Public Function ExportEntityCollection(ByVal collection As IEntityCollection, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of String)) As Object

        Dim mappings As New List(Of ColumnMapping)
        Dim map As ColumnMapping
        For Each name As String In ColumnNames
            map = New ColumnMapping("", name)
            map.TableField.DisplayName = name
            mappings.Add(map)
        Next

        ExportEntityCollection = ExportEntityCollection(collection, Workbook, Worksheet, Range, mappings)
    End Function

    ' exports a collection to a workbook starting at the specified location
    <Obsolete("This method has been deprecated. Please use Excel.Export() overloads.")>
    Public Function ExportEntityCollection(ByVal collection As IEntityCollection, Workbook As String, Worksheet As String, Range As String, ColumnNames As List(Of ColumnMapping)) As Object
        Dim xlProxy As New ExcelHelper()
        Dim wb As Object
        Dim ws As Object
        Dim rg As Object
        Dim result As Object = Nothing

        Try
            wb = xlProxy.Excel.Workbooks.Open(Workbook)
            If Not wb Is Nothing Then
                ws = wb.Worksheets(Worksheet)
                If Not ws Is Nothing Then
                    rg = ws.Range(Range)

                    Dim entityObj As IEntityObject

                    ' get column properties
                    Dim columnCounter As Integer = 0
                    Dim rowCounter As Integer = 0
                    Dim map As ColumnMapping

                    ' add columns names to the list
                    For Each map In ColumnNames
                        If map.TableField.DisplayName.Length > 0 Then
                            rg.Offset(rowCounter, columnCounter).Value = map.TableField.DisplayName
                        Else
                            rg.Offset(rowCounter, columnCounter).Value = map.TableField.Name
                        End If
                        columnCounter += 1
                    Next

                    ' add values on the row following the headers
                    rowCounter = 1

                    ' iterate the collection and extract values by column name
                    For Each entityObj In collection
                        For i As Integer = 0 To ColumnNames.Count - 1
                            Try
                                Dim sValue As Object

                                ' passed in a static value so use that instead
                                If ColumnNames(i).TableField.StaticValue IsNot Nothing Then
                                    sValue = ColumnNames(i).TableField.StaticValue.ToString()
                                Else
                                    sValue = LightSwitchHelper.GetValue(entityObj, ColumnNames(i).TableField.Name)

                                    ' check to see if we have to format the value
                                    If ColumnNames(i).TableField.FormatDelegate IsNot Nothing Then
                                        Try
                                            ' Catch possible improperly formatted delegates
                                            Dim paramType As Type = ColumnNames(i).TableField.FormatDelegate.Method.GetParameters()(0).ParameterType
                                            sValue = ColumnNames(i).TableField.FormatDelegate.DynamicInvoke(Convert.ChangeType(sValue, paramType, Nothing)).ToString()
                                        Catch ex As Exception
                                        End Try
                                    End If
                                End If

                                rg.Offset(rowCounter, i).Value = sValue
                            Catch ex As Exception
                            End Try

                        Next
                        rowCounter += 1
                    Next

                    result = wb
                    xlProxy.ShowWorkbook()

                End If
            End If
        Catch comException As System.Runtime.InteropServices.COMException
            result = Nothing
            xlProxy.Quit()
            Select Case comException.ErrorCode
                Case Is = -2147352565  ' Bad worksheet name
                    Throw New System.ArgumentException("Unknown worksheet", "Worksheet")
                Case Is = -2146827284  ' Bad path parameter or invalid range reference
                    If comException.InnerException Is Nothing Then
                        Throw New System.ArgumentException("Invalid range reference", "Range")
                    Else
                        Throw New System.ArgumentException("Can't open Excel workbook", comException.InnerException)
                    End If
                Case Else
                    Throw comException
            End Select
        End Try

        ExportEntityCollection = result
    End Function

    ' exports collection to a new workbook starting at cell A1 on the first worksheet
    <Obsolete("This method has been deprecated. Please use Excel.Export() overloads.")>
    Public Function ExportEntityCollection(ByVal collection As IEntityCollection) As Object
        Dim result As Object = Nothing

        Try
            Dim excel As New ExcelHelper()
            excel.CreateWorkbook()

            Dim entityObj As IEntityObject
            Dim columnNames As List(Of String) = New List(Of String)

            ' get column properties
            Dim columnProperties As IEnumerable(Of IEntityProperty) = collection(0).Details.Properties.All()

            Dim entityProperty As IEntityProperty
            Dim columnCounter As Integer = 1
            Dim rowCounter As Integer = 1

            ' add columns names to the list
            For Each entityProperty In columnProperties
                columnNames.Add(entityProperty.Name)

                ' add column headers to Excel workbook
                excel.Cells(rowCounter, columnCounter, entityProperty.DisplayName)
                columnCounter += 1
            Next

            ' add values on the row following the headers
            rowCounter = 2

            ' iterate the collection and extract values by column name
            For Each entityObj In collection
                For i As Integer = 0 To columnNames.Count - 1
                    excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, columnNames(i)))
                Next
                rowCounter += 1
            Next

            excel.ShowWorkbook()
            result = excel.Workbook
        Catch ex As Exception
            Throw ex
        End Try

        ExportEntityCollection = result
    End Function
#End Region

    ' Imports a range starting at the location specified by workbook, worksheet, and range.
    ' Workbook should be the fullpath to the workbook.
    Public Sub Import(ByVal collection As IVisualCollection, Workbook As String, Worksheet As String, Range As String)
        _collection = collection

        Dispatchers.Main.BeginInvoke(
          Sub()
              Dim xlProxy As New ExcelHelper()
              Dim wb As Object
              Dim ws As Object
              Dim rg As Object

              wb = xlProxy.Excel.Workbooks.Open(Workbook)
              If Not wb Is Nothing Then
                  ws = wb.Worksheets(Worksheet)
                  If Not ws Is Nothing Then
                      rg = ws.Range(Range)
                      _excelDocRange = ConvertToArray(rg)

                      Dim tablePropertyChoices As List(Of FieldDefinition) = GetTablePropertyChoices()

                      _columnMappings = New List(Of ColumnMapping)
                      Dim numColumns As Int32 = _excelDocRange.GetLength(1)
                      For i = 0 To numColumns - 1
                          _columnMappings.Add(New ColumnMapping(_excelDocRange(0, i), tablePropertyChoices))
                      Next

                      Dim columnMapperContent As New ColumnMapper()
                      columnMapperContent.OfficeColumn.Text = "Excel Column"
                      Dim columnMapperWindow As New ScreenChildWindow()
                      columnMapperContent.DataContext = _columnMappings
                      AddHandler columnMapperWindow.Closed, AddressOf OnMappingDialogClosed

                      'set parent to current screen
                      Dim sdkProxy As IServiceProxy = VsExportProviderService.GetExportedValue(Of IServiceProxy)()
                      columnMapperWindow.Owner = DirectCast(sdkProxy.ScreenViewService.GetScreenView(_collection.Screen).RootUI, Control)
                      columnMapperWindow.Show(_collection.Screen, columnMapperContent)

                  End If
              End If

              rg = Nothing
              ws = Nothing
              wb.Close(False)
              wb = Nothing
              xlProxy.Dispose()
          End Sub)
    End Sub

    ' Imports a range starting at the location specified by workbook, worksheet, and range.
    ' Workbook should be the fullpath to the workbook.
    Public Sub Import(ByVal collection As IVisualCollection, Workbook As String, Worksheet As String, Range As String, ColumnMapping As List(Of ColumnMapping))
        _collection = collection
        _columnMappings = ColumnMapping

        ' Ensure that we have the correct and complete field definition.
        For Each map As OfficeIntegration.ColumnMapping In _columnMappings
            map.TableField = GetFieldDefinition(collection, map.TableField.Name)
        Next

        Dispatchers.Main.BeginInvoke(
          Sub()
              Dim xlProxy As New ExcelHelper()
              Dim wb As Object
              Dim ws As Object
              Dim rg As Object

              wb = xlProxy.Excel.Workbooks.Open(Workbook)
              If Not wb Is Nothing Then
                  ws = wb.Worksheets(Worksheet)
                  If Not ws Is Nothing Then
                      rg = ws.Range(Range)
                      _excelDocRange = ConvertToArray(rg)
                      ValidateData()
                  End If
              End If

              rg = Nothing
              ws = Nothing
              wb.Close(False)
              wb = Nothing
              xlProxy.Dispose()
          End Sub)
    End Sub

    ' Imports a range from workbook chosen by end-user. Assumes table starts at cell A1 on the first worksheet.
    Public Sub Import(ByVal collection As IVisualCollection)
        _collection = collection

        Dispatchers.Main.BeginInvoke(
            Sub()
                Dim dialog As New OpenFileDialog()
                dialog.Multiselect = False
                dialog.Filter = "Excel Documents(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv|All files (*.*)|*.*"
                If dialog.ShowDialog() Then
                    Dim f As FileInfo = dialog.File
                    Try
                        Dim excel As New ExcelHelper()
                        excel.OpenWorkbook(f.FullName)
                        _excelDocRange = excel.UsedRange(1, 1)
                        excel.Dispose()

                        Dim tablePropertyChoices As List(Of FieldDefinition) = GetTablePropertyChoices()

                        _columnMappings = New List(Of ColumnMapping)
                        Dim numColumns As Int32 = _excelDocRange.GetLength(1)
                        For i = 0 To numColumns - 1
                            _columnMappings.Add(New ColumnMapping(_excelDocRange(0, i), tablePropertyChoices))
                        Next

                        Dim columnMapperContent As New ColumnMapper()
                        columnMapperContent.OfficeColumn.Text = "Excel Column"
                        Dim columnMapperWindow As New ScreenChildWindow()
                        columnMapperContent.DataContext = _columnMappings
                        AddHandler columnMapperWindow.Closed, AddressOf OnMappingDialogClosed

                        'set parent to current screen
                        Dim sdkProxy As IServiceProxy = VsExportProviderService.GetExportedValue(Of IServiceProxy)()
                        columnMapperWindow.Owner = DirectCast(sdkProxy.ScreenViewService.GetScreenView(_collection.Screen).RootUI, Control)
                        columnMapperWindow.Show(_collection.Screen, columnMapperContent)

                    Catch ex As SecurityException
                        collection.Screen.Details.Dispatcher.BeginInvoke(
                            Sub()
                                _collection.Screen.ShowMessageBox("Error: Silverlight Security error. Could not load Excel document. Make sure the document is in your 'Documents' directory.")
                            End Sub
                        )
                    Catch comEx As COMException
                        _collection.Screen.Details.Dispatcher.BeginInvoke(
                            Sub()
                                _collection.Screen.ShowMessageBox("Error: Could not open this file.  It may not be a valid Excel document.")
                            End Sub
                        )
                    End Try
                End If
            End Sub)
    End Sub

    ' Imports a range from workbook chosen by end-user with the specified columns. Assumes table starts at cell A1 on the first worksheet.
    Public Sub Import(ByVal collection As IVisualCollection, ColumnMappings As List(Of ColumnMapping))
        _collection = collection
        _columnMappings = ColumnMappings

        ' Ensure that we have the correct and complete field definition.
        For Each map As OfficeIntegration.ColumnMapping In _columnMappings
            map.TableField = GetFieldDefinition(collection, map.TableField.Name)
        Next

        Dispatchers.Main.BeginInvoke(
            Sub()
                Dim dialog As New OpenFileDialog()
                dialog.Multiselect = False
                dialog.Filter = "Excel Documents(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv|All files (*.*)|*.*"
                If dialog.ShowDialog() Then
                    Dim f As FileInfo = dialog.File
                    Try
                        Dim excel As New ExcelHelper()
                        excel.OpenWorkbook(f.FullName)
                        _excelDocRange = excel.UsedRange(1, 1)
                        excel.Dispose()

                        ValidateData()
                    Catch ex As SecurityException
                        collection.Screen.Details.Dispatcher.BeginInvoke(
                            Sub()
                                _collection.Screen.ShowMessageBox("Error: Silverlight Security error. Could not load Excel document. Make sure the document is in your 'Documents' directory.")
                            End Sub
                        )
                    Catch comEx As COMException
                        _collection.Screen.Details.Dispatcher.BeginInvoke(
                            Sub()
                                _collection.Screen.ShowMessageBox("Error: Could not open this file.  It may not be a valid Excel document.")
                            End Sub
                        )
                    End Try
                End If
            End Sub)
    End Sub

    ' Returns an Excel.Application object
    Public Function GetExcel() As Object
        Dim xlProxy As New ExcelHelper
        Dim xl As Object = Nothing

        ' first try and get a reference to a running instance
        If xlProxy.GetExcel Then
            xl = xlProxy.Excel
        Else
            ' next try and create a new instance
            If xlProxy.CreateExcel() Then
                xl = xlProxy.Excel
            Else
                ' can't get Excel - return Nothing by default
            End If
        End If
        GetExcel = xl
    End Function

    ' Returns an Excel.Workbook object
    Public Function GetWorkbook(Excel As Object, WorkbookPath As String) As Object
        Dim wb As Object = Nothing

        ' Validate Word argument is actually Word.Application
        If Not IsExcelApplicationObject(Excel) Then
            Throw New System.ArgumentException("'Excel' is not the expected type of object. Expected object should be an Excel.Application object.", "Excel")
        End If

        If Not File.Exists(WorkbookPath) Then
            Throw New System.ArgumentException("File '" & WorkbookPath & "' does not exist.", "WorkbookPath")
        End If

        Try
            wb = Excel.Workbooks.Open(WorkbookPath)
        Catch ex As Exception
            Throw ex
        End Try

        GetWorkbook = wb
    End Function

    Private Function IsExcelApplicationObject(app As Object) As Boolean
        Dim isValid As Boolean = False
        Dim s As String
        Try
            s = app.Name
            If s = "Microsoft Excel" Then
                isValid = True
            End If
        Catch ex As Exception
        End Try
        IsExcelApplicationObject = isValid
    End Function

    Private Function ConvertToArray(rg As Object) As String(,)
        Dim rowCount As Int32 = rg.Rows.Count
        Dim columnCount As Int32 = rg.Columns.Count

        Dim valueArray(rowCount - 1, columnCount - 1) As String
        For i = 1 To rowCount
            For j = 1 To columnCount
                valueArray(i - 1, j - 1) = rg.Cells(i, j).Value
            Next
        Next
        Return valueArray
    End Function

    Private Sub AddItemsToCollection()
        'This should always be called on the logical thread
        Debug.Assert(_collection.Screen.Details.Dispatcher.CheckAccess(), "Expected to run on the logical thread")

        'Add Items to Collection
        Dim numRows = _excelDocRange.GetLength(0) - 1
        For i = 1 To numRows
            Dim currentRow As Int32 = i
            Dim newRow As IEntityObject = _collection.AddNew()
            Dim nOfficeColumnIndex As Int32
            For Each mapping As ColumnMapping In _columnMappings
                If mapping.TableField IsNot Nothing AndAlso mapping.TableField.Name <> "<Ignore>" Then
                    nOfficeColumnIndex = GetColumnIndex(mapping.OfficeColumn)
                    If nOfficeColumnIndex >= 0 Then
                        Dim value As String = _excelDocRange(currentRow, nOfficeColumnIndex)
                        If String.IsNullOrEmpty(value) AndAlso mapping.TableField.IsNullable Then
                            newRow.Details.Properties(mapping.TableField.Name).Value = Nothing
                        ElseIf mapping.TableField.EntityType IsNot Nothing Then
                            'Get cached results
                            If navProperties.ContainsKey(mapping.TableField.Name + "_" + value) Then
                                newRow.Details.Properties(mapping.TableField.Name).Value = navProperties(mapping.TableField.Name + "_" + value)
                            End If
                        Else
                            TryConvertValue(mapping.TableField.TypeName, value, newRow.Details.Properties(mapping.TableField.Name).Value)
                        End If
                    End If
                End If
            Next
        Next
    End Sub

    Private Sub ValidateData()
        Dim errorList As New List(Of String)
        Dim numRows = _excelDocRange.GetLength(0) - 1

        'Dispatch to the logical thread
        _collection.Screen.Details.Dispatcher.BeginInvoke(
            Sub()
                Try
                    'Work through each row of data
                    For i = 1 To numRows

                        Dim currentRow As Int32 = i
                        Dim count As Int32 = 0
                        Dim nOfficeColumnIndex As Int32

                        ' Work through each mapped column
                        For Each mapping As ColumnMapping In _columnMappings

                            ' Make sure the current column should be processed
                            If mapping.TableField IsNot Nothing AndAlso mapping.TableField.Name <> "<Ignore>" Then

                                nOfficeColumnIndex = GetColumnIndex(mapping.OfficeColumn)

                                If nOfficeColumnIndex >= 0 Then

                                    ' Read in the value for the current row and column
                                    Dim value As String = _excelDocRange(currentRow, nOfficeColumnIndex)

                                    ' Process values without null or empty string values
                                    If Not (String.IsNullOrEmpty(value)) Then

                                        ' Try and validate based on the Entity type
                                        Dim isValid As Boolean
                                        If mapping.TableField.EntityType IsNot Nothing Then
                                            isValid = ValidData(value, currentRow, mapping, _collection, navProperties, errorList)
                                        Else
                                            isValid = ValidData(value, currentRow, mapping, errorList)
                                        End If

                                    ElseIf String.IsNullOrEmpty(value) _
                                        AndAlso mapping.TableField.IsNullable = False _
                                        AndAlso mapping.TableField.EntityType Is Nothing _
                                        AndAlso mapping.TableField.TypeName <> "String" Then

                                        errorList.Add("Column: " + mapping.OfficeColumn + _
                                                      " Row:" + currentRow.ToString + _
                                                      " A value must be specified for " + _
                                                      mapping.TableField.DisplayName + _
                                                      ". A default value will be used.")

                                    End If

                                Else
                                    ' Couldn't find the column in the Excel range
                                    errorList.Add("Column: " + mapping.OfficeColumn + _
                                                  " Row:" + currentRow.ToString + _
                                                  " Could not locate column in Office document.")
                                End If
                            End If
                            count = count + 1
                        Next
                    Next
                    If errorList.Count > 0 Then
                        DisplayErrors(errorList)
                    Else
                        'Add Items to Collection
                        AddItemsToCollection()
                    End If
                Catch ex As Exception
                    Throw ex
                End Try
            End Sub)
    End Sub

    Private Function GetColumnIndex(sOfficeColumnName As String) As Int32
        Dim nResult As Int32 = -1
        Dim nColumn As Int32

        For nColumn = 0 To _excelDocRange.GetLength(1) - 1
            If _excelDocRange(0, nColumn) = sOfficeColumnName Then
                nResult = nColumn
                Exit For
            End If
        Next
        GetColumnIndex = nResult
    End Function

    Private Sub DisplayErrors(ByVal errorList As List(Of String))
        Dispatchers.Main.BeginInvoke(Sub()
                                         'Display some sort of dialog indicating that errors occurred
                                         Dim sdkProxy As IServiceProxy = VsExportProviderService.GetExportedValue(Of IServiceProxy)()
                                         Dim errorDialog As New ErrorList()
                                         Dim errorWindow As New ScreenChildWindow()
                                         errorDialog.DataContext = errorList
                                         errorWindow.DataContext = errorList
                                         errorWindow.Owner = DirectCast(sdkProxy.ScreenViewService.GetScreenView(_collection.Screen).RootUI, Control)

                                         AddHandler errorWindow.Closed, AddressOf OnErroDialogClosed
                                         errorWindow.Show(_collection.Screen, errorDialog)
                                     End Sub)
    End Sub

#Region "Dialog Closing Methods"
    Private Sub OnErroDialogClosed(ByVal sender As Object, ByVal e As EventArgs)
        Dim errorWindow As ScreenChildWindow = sender
        Dim result As Boolean? = errorWindow.DialogResult
        If result.HasValue AndAlso result.Value = True Then
            _collection.Screen.Details.Dispatcher.BeginInvoke(Sub()
                                                                  AddItemsToCollection()
                                                              End Sub)
        End If
    End Sub

    Private Sub OnMappingDialogClosed(ByVal sender As Object, ByVal e As EventArgs)
        Dim mappingWindow As ScreenChildWindow = sender
        Dim result As Boolean? = mappingWindow.DialogResult
        If result.HasValue AndAlso result.Value = True Then
            ValidateData()
        End If
    End Sub
#End Region

    Private Function GetTablePropertyChoices() As List(Of FieldDefinition)
        Dim entityType As IEntityType = _collection.Details.GetModel.ElementType
        Dim tablePropertyChoices As New List(Of FieldDefinition)

        For Each p As IEntityPropertyDefinition In entityType.Properties
            'Ignore hidden fields and computed field
            If p.Attributes.Where(Function(a) a.Class.Name = "Computed").FirstOrDefault Is Nothing Then
                If Not TypeOf p.PropertyType Is ISequenceType Then
                    'ignore collections and entities
                    Dim fd As New FieldDefinition()
                    fd.Name = p.Name
                    fd.DisplayName = p.Name

                    Dim isNullable As Boolean
                    fd.TypeName = GetPropertyType(p.PropertyType, isNullable)
                    fd.IsNullable = isNullable
                    If fd.TypeName = "Entity" Then
                        fd.EntityType = DirectCast(p.PropertyType, IEntityType)
                    End If

                    tablePropertyChoices.Add(fd)
                End If
            End If
        Next
        tablePropertyChoices.Add(New FieldDefinition("<Ignore>", "<Ignore>", "", False))
        Return tablePropertyChoices
    End Function
End Module




